package com.walker.service.catalogue;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.walker.util.CommonUtils;

@Service
public class CatalogueFuncService {

	@Autowired
	@Qualifier("dmJdbcTemplate")
	private JdbcTemplate dmJdbcTemplate;
	
	/**
	 * 函数列表
	 * @param params
	 * @return
	 */
	public Map<String,Object> queryFuncList(Map<String,Object> params){
		String keyword = CommonUtils.valueOf(params.get("keyword"));
		String classify_id = CommonUtils.valueOf(params.get("classify_id"));
		int pageSize = Integer.parseInt(CommonUtils.valueOf(params.get("pageSize")));
		int pageNo = Integer.parseInt(CommonUtils.valueOf(params.get("pageNo")));
		int offset = (pageNo - 1) * pageSize ;
		
		List<Object> paramList = new ArrayList<Object>();
		
		String countSql = getFuncCountSql(paramList,classify_id,keyword);
		Object totalRecords = dmJdbcTemplate.queryForMap(countSql,paramList.toArray()).get("count");
		
		String sql = getFuncSql(classify_id,keyword);
		paramList.add(pageSize);
		paramList.add(offset);
		List<Map<String,Object>> deriList = dmJdbcTemplate.queryForList(sql,paramList.toArray());
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("total_records", totalRecords);
		data.put("func_data", deriList);
		
		return data;
	}

	/**
	 * 函数类型信息
	 * @return
	 */
	public List<Map<String,Object>> queryFuncClassify(){
		String sql = "select id as classify_id,classify_name from t_meta_func_classify";
		return dmJdbcTemplate.queryForList(sql);
	}
	
	/**
	 * 函数详情信息
	 * @param id
	 * @return
	 */
	public Map<String,Object> queryFuncDetail(String id){
		
		String sql = "select func_name,'-' as product_table,p.project_code,f.create_user from t_meta_func f left join t_meta_project p on f.project_id = p.id where f.id = ?";
		List<Map<String,Object>> rel_detail =  dmJdbcTemplate.queryForList(sql,id);
		
		sql = "select '函数创建' as update_event,modify_user,date_format(modify_time,'%Y-%m-%d %H:%i:%s') as modify_time,help_text as public_desc from t_meta_func where id = ?";
		List<Map<String,Object>> update_detail =  dmJdbcTemplate.queryForList(sql,id);
		
		StringBuffer baseSql = new StringBuffer();
		baseSql.append("select func_name,");
		baseSql.append("       func.id,");
		baseSql.append("       project.project_name,");
		baseSql.append("       classify.classify_name,");
		baseSql.append("       create_user,");
		baseSql.append("       '自定义函数' as func_source,");
		baseSql.append("       date_format(modify_time,'%Y-%m-%d %H:%i:%s') as modify_time,");
		baseSql.append("       class_main,");
		baseSql.append("       func_format,");
		baseSql.append("       help_text,");
		baseSql.append("       date_format(func.create_time,'%Y-%m-%d %H:%i:%s') as create_time");
		baseSql.append("  from t_meta_func func");
		baseSql.append("  left join t_meta_project project");
		baseSql.append("    on func.project_id = project.id");
		baseSql.append("  left join t_meta_func_classify classify");
		baseSql.append("    on func.func_classify = classify.id");
		baseSql.append("  where func.id = ?");
		Map<String,Object> baseInfo = dmJdbcTemplate.queryForMap(baseSql.toString(),id);
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("rel_data",rel_detail);
		data.put("update_data",update_detail);
		data.put("basic_data", baseInfo);
		
		return data;
	}
	
	
	private String getFuncSql(String classify_id,String keyword) {
		StringBuffer sql = new StringBuffer();
		sql.append("select func_code,func_name,");
		sql.append("       func.id,");
		sql.append("       project.project_name,");
		sql.append("       classify.classify_name,");
		sql.append("       create_user,");
		sql.append("       '自定义函数' as func_source,");
		sql.append("       date_format(modify_time,'%Y-%m-%d %H:%i:%s') as modify_time,");
		sql.append("       class_main,");
		sql.append("       func_format,");
		sql.append("       help_text,");
		sql.append("       date_format(func.create_time,'%Y-%m-%d %H:%i:%s') as create_time");
		sql.append("  from t_meta_func func");
		sql.append("  left join t_meta_project project");
		sql.append("    on func.project_id = project.id");
		sql.append("  left join t_meta_func_classify classify");
		sql.append("    on func.func_classify = classify.id");
		sql.append("  where 1 = 1");
		if(StringUtils.isNotBlank(classify_id)){
			sql.append(" and func.func_classify = ?");
		}
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and func_name like ?");
		}
		sql.append("  order by modify_time desc");
		sql.append(" limit ? offset ? ");
		return sql.toString();
	}
	
	private String getFuncCountSql(List<Object> paramList,String classify_id,String keyword) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) count from t_meta_func where 1 = 1");
		if(StringUtils.isNotBlank(classify_id)){
			sql.append(" and func_classify = ?");
			paramList.add(classify_id);
		}
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and func_name like ?");
			paramList.add("%"+keyword+"%");
		}
		return sql.toString();
	}
}